create or replace view v_audiovedio as
select a.dataid,
       a.archivenum,
       a.tempordernum,
       a.archivesource,
       a.title,
       c.archivemenu,
       a.publisher,
       a.publishtime,
       a.fileorgid,
       d.code_desc f_keeptime,
       e.code_desc f_carriertype,
       f.code_desc f_securitytype,
       g.code_desc f_keepsecuritytime,
       a.summary,
       a.remark,
       a.createuserid,
       a.DOCID,
       a.title||'^'||a.publisher||'^'||a.fileorgid||'^'||a.summary||'^'||b.subtitle||'^'||e.code_desc||'^'||d.code_desc||'^'|| f.code_desc||'^'||g.code_desc search_val
from audiovedio_archive  a
left join
(
     select b.f_archiveid,
            wmsys.wm_concat(to_char(b.title)) subtitle
     from pagefile b
     group by b.f_archiveid
) b on a.dataid = b.f_archiveid
left join archivemenu c on a.archivetype = c.typeid
left join p_bt_codetable d on a.f_keeptime = d.codetableid
left join p_bt_codetable e on a.f_carriertype = e.codetableid
left join p_bt_codetable f on a.f_securitytype = f.codetableid
left join p_bt_codetable g on a.f_keepsecuritytime = g.codetableid
where a.statusid in(27,51,54,57,137,158,161,164);

